﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using FWK.DataLayer.Base;
using CDB.Model.Colunas;
using System.Collections.ObjectModel;
using System.Data;

namespace CDB.DataLayer.Colunas
{
    public class DLColuna:FwDLBase
    {
        #region Construtor
        public DLColuna(string pstrConnection, string pstrProvider)
        {
            this.objFwProvider = new FwProvider(pstrProvider, pstrConnection);
        }
        #endregion

        #region Métodos

        #region Listar
        public Collection<Coluna> Listar(string pstrNomeTabela)
        {
            string strPK = this.ObterPK(pstrNomeTabela);
            Collection<Coluna> colColuna = new Collection<Coluna>();
            StringBuilder strQuery = new StringBuilder();

            strQuery.Append("SELECT "); 
            strQuery.Append("COLUNAS.NAME AS NOME, ");
            strQuery.Append("TIPOS.NAME AS TIPO, ");
            strQuery.Append("COLUNAS.LENGTH AS TAMANHO, ");
            strQuery.Append("COLUNAS.ISNULLABLE AS ISNULL  ");
            strQuery.Append("FROM  ");
            strQuery.Append("SYSOBJECTS AS TABELAS, ");
            strQuery.Append("SYSCOLUMNS AS COLUNAS, ");
            strQuery.Append("SYSTYPES   AS TIPOS ");
            strQuery.Append("WHERE  ");
            strQuery.Append("TABELAS.ID = COLUNAS.ID ");
            strQuery.Append("AND COLUNAS.USERTYPE = TIPOS.USERTYPE ");
            strQuery.Append("AND TABELAS.NAME = @NOME");

            objFwCommand = new FwCommand();
            objFwCommand.CommandText = strQuery.ToString();

            objFwCommand.Parameters.Add(new FwParameter("@NOME",pstrNomeTabela,FwDbType.Varchar,50));

            IDataReader objIDataReader = objFwProvider.ExecuteReader(objFwCommand);

            while (objIDataReader.Read())
            {
                Coluna objColuna = new Coluna();
                objColuna.FromIDataReader(objIDataReader);
                objColuna.PK = objColuna.Nome == strPK;
                colColuna.Add(objColuna);
            }
            objIDataReader.Close();

            return colColuna;
        }
        #endregion

        #region ObterPK
        private string ObterPK(string pstrNomeTabela)
        {
            StringBuilder strQuery = new StringBuilder();

            strQuery.Append("SELECT C.COLUMN_NAME "); 
            strQuery.Append("FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK , ");
            strQuery.Append("INFORMATION_SCHEMA.KEY_COLUMN_USAGE C ");
            strQuery.Append("WHERE 	 ");
            strQuery.Append("CONSTRAINT_TYPE = 'PRIMARY KEY' ");
            strQuery.Append("AND	C.TABLE_NAME = PK.TABLE_NAME ");
            strQuery.Append("AND	C.CONSTRAINT_NAME = PK.CONSTRAINT_NAME ");
            strQuery.Append("AND PK.TABLE_NAME = @NOMETABELA ");
            

            objFwCommand = new FwCommand();
            objFwCommand.CommandText = strQuery.ToString();

            objFwCommand.Parameters.Add(new FwParameter("@NOMETABELA", pstrNomeTabela, FwDbType.Varchar, 50));

            IDataReader objIDataReader = objFwProvider.ExecuteReader(objFwCommand);

            string strNomeColunaPK = string.Empty;
            while (objIDataReader.Read())
            {
                strNomeColunaPK = objIDataReader.GetString(objIDataReader.GetOrdinal("COLUMN_NAME"));
            }
            objIDataReader.Close();
            return strNomeColunaPK;
        }
        #endregion
        #endregion
    }
}
